<?php

namespace Application\Model;

use Zend\Db\Adapter\Adapter;
use Zend\Db\Sql\Sql;
use Zend\Db\ResultSet\ResultSet;
use Zend\Db\TableGateway\TableGateway;
use Zend\Db\Sql\Select;
use Application\Model\SejourTable;

class RegionsTable
{
    protected $table ='Regions';
    protected $tableName ='Regions';
    
    protected $tableGateway;
    protected $sejourTable;

    public function __construct(TableGateway $tableGateway, SejourTable $sejourTable)
    {
        $this->tableGateway = $tableGateway;
        $this->sejourTable = $sejourTable;
    }
    
    public function fetchAll()
    {
    	$resultSet = $this->tableGateway->select(function(Select $select){
	        $select->order('libelleRegions ASC');
	    });
    	
    	return $resultSet;
    }
    
    public function fetchAllArray()
    {
        $resultSet = $this->fetchAll();
        $entities = array();
        
        foreach ($resultSet as $row) {
        	$obj = new Regions();
        	$obj->exchangeArray( (array) $row);
        	$entities[] = $obj;
        }
        
        return $entities;
    }

    /* retourne toutes les régions utilisés dans des séjours */
    public function fetchAllUsed()
    {
        $resultSet = $this->tableGateway->select(function(Select $select){
                $select->quantifier('DISTINCT')
                ->join('Sejour', 'Sejour.regionId = Regions.idRegions', array(), 'inner')
                ->order('libelleRegions ASC');
        });
        
        return $this->toArray ($resultSet);
    }

    public function toArray ($resultSet)
    {
        $entities = array();
        
        foreach ($resultSet as $row) {
            $obj = new Regions();
            $obj->exchangeArray( (array) $row);
            $entities[] = $obj;
        }
        
        return $entities;
    }

    public function initFiltresRegions ( $values )
    {
        $sql = new Sql($this->tableGateway->getAdapter());
        $select = $sql->select();
        
        // print_r($values);

        $select->from($this->tableGateway->table)
                // ->join('Pays', 'Pays.idPays = Regions.idPays', array('libellePays' => 'libellePays'), 'inner')
                ->where->in("Regions.idRegions", $values);

        $statement = $sql->prepareStatementForSqlObject($select);
        $resultSet = $statement->execute();

        return $this->toArray ($resultSet);
    }

    public function getRegion($id)
     {
         $id  = (int) $id;
         $rowset = $this->tableGateway->select(array('idRegions' => $id));
         $row = $rowset->current();
         if (!$row) {
             throw new \Exception("Could not find row $id");
         }
         return $row;
     }

     public function listeRegionsParIdPays( $idPays )
     {
        $resultSet = $this->tableGateway->select(function(Select $select) use($idPays) {
            $select->quantifier('DISTINCT')
                    ->join('Sejour', 'Sejour.regionId = Regions.idRegions', array(), 'inner')
                    ->where->equalTo('idPays', $idPays);
            $select->order('libelleRegions ASC');

            // echo $select->getSqlString();die;
        });
        
        return $this->toArray ($resultSet);
     }

    public function getRegionByLibelle( $libelle )
    {
        $rowset = $this->tableGateway->select(array('libelleRegions' => ucfirst( $libelle ) ));
        $row = $rowset->current();
        
        return $row;
    }
}